show databases ;
use demo;
show tables;

SELECT * FROM mysql.user;
SELECT * FROM bonus;
SELECT * FROM dept;

select * from emp ;


desc table dept;

SELECT * FROM emp where EMPNO in (7369,7499) ;
SELECT * FROM salgrade;

insert emp(EMPNO, ENAME) select '1', '员工1';

select 1 from dual;
select '张三3', 'java研发', 10000,111;

truncate table bonus;
SELECT * FROM bonus;

insert bonus(ename, job, sal) values ('张三2', 'java研发2', 12000);
insert bonus select '张三3', 'java研发', 10000,111;

SELECT * FROM emp where ENAME in ('SMITH','ALLEN');
SELECT * FROM emp;

select * from emp where empno in (7369,7499);
insert emp(EMPNO, ENAME) values (2, 2);
insert emp(EMPNO, ENAME) values (3, 3);

-- --------事务-----

begin ;
-- insert update
commit ;


-- --------简单查询-----
SELECT empno,ename FROM emp;
# 起别名
SELECT empno AS 员工编号,ename AS 员工姓名 FROM emp;
-- 起别名
SELECT empno 员工编号,ename '姓 名' FROM emp;

# 字符串类型可以做连续运算
select concat('1', '2', '3') as ss from dual;
SELECT concat('我的名字是：',ename,'!') 运算之后的名字 FROM emp;
# 去重
SELECT job FROM emp;
SELECT DISTINCT job FROM emp;

SELECT DISTINCT deptno,job FROM emp;

-- --------条件查询-----

select * from emp  where EMPNO = '7369';

SELECT * FROM emp;
update emp set sal = sal+100 where ename = 'SMITH';
select * from emp where ename = '' or 1=1;

select * from emp where ename = 'SMITH';

SELECT * FROM emp WHERE hiredate > '1982-01-01';
select * from emp where COMM='300';
SELECT * FROM emp WHERE deptno=30 OR sal>3000;
SELECT * FROM emp WHERE job IN('president','manager','analyst');

-- --------模糊查询-----
SELECT * FROM emp WHERE ename LIKE 'S%';
SELECT * FROM emp WHERE ename LIKE '__a%';

-- --------排序-----

SELECT * FROM emp ORDER BY sal asc ;
SELECT * FROM emp ORDER BY sal DESC;
SELECT * FROM emp ORDER BY deptno DESC,sal DESC;

-- --------统计函数-----

SELECT SUM(sal) 30号部门工资的和,AVG(sal) 平均工资,MAX(sal) 最高工资,MIN(sal)最低工资,COUNT(*) 部门人数 FROM emp WHERE deptno=30;
SELECT * FROM emp WHERE deptno=30;


-- --------分组-----
SELECT deptno FROM emp GROUP BY deptno;

SELECT deptno, MAX(sal),MIN(sal) FROM emp GROUP BY deptno;

SELECT deptno,job,MAX(sal),MIN(sal) FROM emp GROUP BY deptno,job;

SELECT deptno,COUNT(*) 部门的人数 FROM emp GROUP BY deptno;

SELECT deptno,COUNT(*) 部门的人数 FROM emp GROUP BY deptno HAVING COUNT(*) >5;

#按照部门来分组，查询每个部门的最高工资，最低工资，平均工资。
#求每种工作的最高薪资，最低薪资，以及人数。
SELECT job,MAX(sal),MIN(sal),COUNT(*) FROM emp GROUP BY job;


-- 分页
SELECT * FROM emp ORDER BY sal DESC LIMIT 0,5;
SELECT * FROM emp ORDER BY sal DESC LIMIT 5,5;
SELECT * FROM emp ORDER BY sal DESC LIMIT 10,5;

-- 多表联查
SELECT * FROM emp;
SELECT * FROM dept;

SELECT d.DEPTNO FROM dept d;

SELECT e.*,d.* FROM emp e,dept d;

-- 内连接 交集
SELECT e.*,d.* FROM emp e,dept d WHERE e.`DEPTNO` = d.`DEPTNO`;
SELECT e.*,d.`DNAME` FROM emp e,dept d WHERE e.`DEPTNO` = d.`DEPTNO`;
SELECT e.*,d.`DNAME` FROM emp e INNER JOIN dept d ON e.`DEPTNO` = d.`DEPTNO`;

-- 外连接 并集 full join
SELECT * FROM emp; -- 14
SELECT * FROM dept; -- 4


-- 做外连接左表一定要全部显示
SELECT e.*,d.* FROM emp e LEFT JOIN dept d ON e.`DEPTNO` = d.`DEPTNO`;

select e.*
from emp e
right join dept d on e.DEPTNO = d.DEPTNO;

-- 做外连接左表一定要全部显示
SELECT e.*,d.* FROM dept d  RIGHT JOIN emp e ON e.`DEPTNO` = d.`DEPTNO`;

-- 查询所有的部门，以及对应的员工信息。
SELECT d.*,e.* FROM dept d LEFT JOIN emp e ON d.`DEPTNO` = e.`DEPTNO`;

-- 查询每个员工的员工信息，工资等级。emp，salgrade
SELECT e.*,s.`GRADE` FROM emp e LEFT JOIN salgrade s ON e.`SAL` BETWEEN s.`LOSAL` AND s.`HISAL`;

-- 查询每个员工的员工信息，部门名称，部门位置，工资等级

SELECT e.*,d.`DNAME`,d.`LOC`,s.`GRADE` FROM emp e
                                                LEFT JOIN dept d ON e.`DEPTNO` = d.`DEPTNO`
                                                LEFT JOIN salgrade s ON e.`SAL` BETWEEN s.`LOSAL` AND s.`HISAL`
where e.EMPNO in (7369,7499)
;
-- 查询在部门在纽约的员工信息，部门名称，工资等级。

SELECT e.*,d.`DNAME`,s.`GRADE` FROM emp e
                                        LEFT JOIN dept d ON e.`DEPTNO`=d.`DEPTNO`
                                        LEFT JOIN salgrade s ON e.`SAL` BETWEEN s.`LOSAL` AND s.`HISAL`
WHERE d.`LOC` = 'NEW YORK';

-- 查询每个部门的人数，部门名称，部门编号。
SELECT e.`DEPTNO`,d.`DNAME`,COUNT(*) 部门的人数 FROM emp e LEFT JOIN dept d ON e.`DEPTNO` = d.`DEPTNO`
GROUP BY e.`DEPTNO`,d.`DNAME`;


-- 子查询
SELECT * FROM dept d WHERE d.`LOC` = 'NEW YORK';

-- 放在where后面
-- 子查询的结果是多行数据

SELECT * FROM emp e WHERE e.`DEPTNO` IN (SELECT deptno FROM dept d WHERE d.`LOC` = 'NEW YORK');


-- 放在from后面
-- 子查询的结果是多行数据
select * from ( SELECT * FROM dept d WHERE d.`LOC` = 'NEW YORK' ) t;

SELECT e.*,t.dname FROM
    (SELECT * FROM dept d WHERE d.`LOC` = 'NEW YORK') t
        INNER JOIN emp e ON t.deptno = e.`DEPTNO`;

-- 查询比allen工资高的员工信

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'allen');
-- 查询工资不是最高的，也不是最低的员工信息。
SELECT MAX(sal) FROM emp; -- 5000
SELECT MIN(sal) FROM emp; -- 800

SELECT * FROM emp WHERE sal != (SELECT MIN(sal) FROM emp) AND sal != (SELECT MAX(sal) FROM emp);

-- 不是销售部的员工信息
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname != 'SALES');

-- 查询员工信息，要求工资高于部门编号为10的中的任意员工即可


SELECT * FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno  =20);


-- 查询员工信息，工资大于30部门的所有人
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno  =30);

-- 查询本公司工资最高的员工详细信息
SELECT MAX(sal) FROM emp ;

SELECT * FROM emp WHERE sal =  (SELECT MAX(sal) FROM emp );
